package model

import (
	"context"
	"fmt"
	"time"

	"github.com/zeromicro/go-zero/core/stores/cache"
	"github.com/zeromicro/go-zero/core/stores/sqlx"
)

var _ TopicModel = (*customTopicModel)(nil)

type (
	// TopicModel is an interface to be customized, add more methods here,
	// and implement the added methods in customTopicModel.
	TopicModel interface {
		topicModel
		GetUserRankingWithCache(ctx context.Context, limit int64) ([]UserRankingItem, error)
	}

	customTopicModel struct {
		*defaultTopicModel
		conn sqlx.SqlConn
	}
)

// UserRankingItem 用户排行榜
type UserRankingItem struct {
	UserName      string    `db:"userName" json:"userName"`
	Nickname      string    `db:"nickname" json:"nickname"`
	AvatarName    string    `db:"avatarName" json:"avatarName"`
	AvatarUrl     string    `db:"avatar_url" json:"avatarUrl"`
	ArticleCount  int64     `db:"article_count" json:"articleCount"`
	TotalViews    int64     `db:"total_views" json:"totalViews"`
	TotalComments int64     `db:"total_comments" json:"totalComments"`
	LastPostTime  time.Time `db:"last_post_time" json:"lastPostTime"`
	Score         int64     `db:"score" json:"score"`
}

// NewTopicModel returns a model for the database table.
func NewTopicModel(conn sqlx.SqlConn, c cache.CacheConf, opts ...cache.Option) TopicModel {
	return &customTopicModel{
		defaultTopicModel: newTopicModel(conn, c, opts...),
		conn:              conn,
	}
}

// GetUserRankingByArticleCount 根据文章数量获取用户排行榜
func (m *customTopicModel) GetUserRankingByArticleCount(ctx context.Context, limit int64) ([]UserRankingItem, error) {
	query := `
		SELECT 
			u.userName,
			u.nickname,
			u.avatarName,
			CASE 
				WHEN u.avatarName IS NULL OR u.avatarName = '' 
				THEN CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/default-avatar.png')
				ELSE CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/avatar/', u.avatarName)
			END AS avatar_url,
			COUNT(t.id) AS article_count,
			SUM(t.viewTotal) AS total_views,
			SUM(t.commentTotal) AS total_comments,
			MAX(t.postTime) AS last_post_time,
			COUNT(t.id) AS score
		FROM user u
		LEFT JOIN topic t ON u.userName = t.userName
		WHERE t.status = 20
			AND t.allow = 1
		GROUP BY u.userName, u.nickname, u.avatarName
		HAVING COUNT(t.id) >= 1
		ORDER BY COUNT(t.id) DESC, SUM(t.viewTotal) DESC
		LIMIT ?
	`

	var result []UserRankingItem
	err := m.conn.QueryRowsCtx(ctx, &result, query, limit)
	return result, err
}

// GetUserRankingByViews 根据浏览量获取用户排行榜
func (m *customTopicModel) GetUserRankingByViews(ctx context.Context, limit int64) ([]UserRankingItem, error) {
	query := `
		SELECT 
			u.userName,
			u.nickname,
			u.avatarName,
			CASE 
				WHEN u.avatarName IS NULL OR u.avatarName = '' 
				THEN CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/default-avatar.png')
				ELSE CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/avatar/', u.avatarName)
			END AS avatar_url,
			COUNT(t.id) AS article_count,
			SUM(t.viewTotal) AS total_views,
			SUM(t.commentTotal) AS total_comments,
			MAX(t.postTime) AS last_post_time,
			SUM(t.viewTotal) AS score
		FROM user u
		LEFT JOIN topic t ON u.userName = t.userName
		WHERE t.status = 20
			AND t.allow = 1
		GROUP BY u.userName, u.nickname, u.avatarName
		HAVING COUNT(t.id) >= 1
		ORDER BY SUM(t.viewTotal) DESC, COUNT(t.id) DESC
		LIMIT ?
	`

	var result []UserRankingItem
	err := m.conn.QueryRowsCtx(ctx, &result, query, limit)
	return result, err
}

// GetUserRankingByComprehensiveScore 根据综合评分获取用户排行榜
func (m *customTopicModel) GetUserRankingByComprehensiveScore(ctx context.Context, limit int64) ([]UserRankingItem, error) {
	query := `
		SELECT 
			u.userName,
			u.nickname,
			u.avatarName,
			CASE 
				WHEN u.avatarName IS NULL OR u.avatarName = '' 
				THEN CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/default-avatar.png')
				ELSE CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/avatar/', u.avatarName)
			END AS avatar_url,
			COUNT(t.id) AS article_count,
			SUM(t.viewTotal) AS total_views,
			SUM(t.commentTotal) AS total_comments,
			MAX(t.postTime) AS last_post_time,
			(COUNT(t.id) * 10 + SUM(t.viewTotal) + SUM(t.commentTotal) * 5) AS score
		FROM user u
		LEFT JOIN topic t ON u.userName = t.userName
		WHERE t.status = 20
			AND t.allow = 1
		GROUP BY u.userName, u.nickname, u.avatarName
		HAVING COUNT(t.id) >= 1
		ORDER BY score DESC
		LIMIT ?
	`

	var result []UserRankingItem
	err := m.conn.QueryRowsCtx(ctx, &result, query, limit)
	return result, err
}

// GetUserRankingWithQuestion 包含问答内容的综合排行榜
func (m *customTopicModel) GetUserRankingWithQuestion(ctx context.Context, limit int64) ([]UserRankingItem, error) {
	query := `
		SELECT 
			u.userName,
			u.nickname,
			u.avatarName,
			CASE 
				WHEN u.avatarName IS NULL OR u.avatarName = '' 
				THEN CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/default-avatar.png')
				ELSE CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/avatar/', u.avatarName)
			END AS avatar_url,
			COUNT(t.id) AS article_count,
			COUNT(q.id) AS question_count,
			SUM(t.viewTotal) AS total_views,
			SUM(t.commentTotal) AS total_comments,
			MAX(t.postTime) AS last_post_time,
			(COUNT(t.id) + COUNT(q.id)) AS score
		FROM user u
		LEFT JOIN topic t ON u.userName = t.userName AND t.status = 20 AND t.allow = 1
		LEFT JOIN question q ON u.userName = q.userName AND q.status = 20 AND q.allow = 1
		GROUP BY u.userName, u.nickname, u.avatarName
		HAVING (COUNT(t.id) + COUNT(q.id)) >= 1
		ORDER BY (COUNT(t.id) + COUNT(q.id)) DESC, SUM(t.viewTotal) DESC
		LIMIT ?
	`

	var result []UserRankingItem
	err := m.conn.QueryRowsCtx(ctx, &result, query, limit)
	return result, err
}

// GetUserRankingByTimeRange 根据时间范围获取用户排行榜
func (m *customTopicModel) GetUserRankingByTimeRange(ctx context.Context, startTime, endTime time.Time, limit int64) ([]UserRankingItem, error) {
	query := `
		SELECT 
			u.userName,
			u.nickname,
			IFNULL(u.avatarName, '') AS avatarName,
			CASE 
				WHEN u.avatarName IS NULL OR u.avatarName = '' 
				THEN CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/default-avatar.png')
				ELSE CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/avatar/', u.avatarName)
			END AS avatar_url,
			COUNT(t.id) AS article_count,
			SUM(t.viewTotal) AS total_views,
			SUM(t.commentTotal) AS total_comments,
			MAX(t.postTime) AS last_post_time,
			COUNT(t.id) AS score
		FROM user u
		LEFT JOIN topic t ON u.userName = t.userName
		WHERE t.status = 20 
			AND t.allow = 1
			AND t.postTime BETWEEN ? AND ?
		GROUP BY u.userName, u.nickname, u.avatarName
		HAVING COUNT(t.id) >= 1
		ORDER BY COUNT(t.id) DESC, SUM(t.viewTotal) DESC
		LIMIT ?
	`

	var result []UserRankingItem
	err := m.conn.QueryRowsCtx(ctx, &result, query, startTime, endTime, limit)
	return result, err
}

// GetUserRankingWithCache 带缓存的用户排行榜
func (m *customTopicModel) GetUserRankingWithCache(ctx context.Context, limit int64) ([]UserRankingItem, error) {
	cacheKey := fmt.Sprintf("user_ranking:%d", limit)

	// 尝试从缓存获取
	var result []UserRankingItem
	err := m.CachedConn.QueryRowCtx(ctx, &result, cacheKey, func(ctx context.Context, conn sqlx.SqlConn, v any) error {
		fmt.Println("GetUserRankingWithCache from mysql:")
		query := `
			SELECT 
				u.userName,
				u.nickname,
				IFNULL(u.avatarName, '') AS avatarName,
				CASE 
					WHEN u.avatarName IS NULL OR u.avatarName = '' 
					THEN CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/default-avatar.png')
					ELSE CONCAT('https://your-bucket.oss-cn-region.aliyuncs.com/avatar/', u.avatarName)
				END AS avatar_url,
				COUNT(t.id) AS article_count,
				SUM(t.viewTotal) AS total_views,
				SUM(t.commentTotal) AS total_comments,
				MAX(t.postTime) AS last_post_time,
				COUNT(t.id) AS score
			FROM user u
			LEFT JOIN topic t ON u.userName = t.userName
			WHERE t.status = 20 
				AND t.allow = 1
			GROUP BY u.userName, u.nickname, u.avatarName
			HAVING COUNT(t.id) >= 1
			ORDER BY COUNT(t.id) DESC, SUM(t.viewTotal) DESC
			LIMIT ?
		`

		// 使用传入的conn执行查询
		return conn.QueryRowsCtx(ctx, v, query, limit)
	})
	if err != nil {
		fmt.Printf("error GetUserRankingWithCache:%s", err.Error())
		return result, nil
	}
	fmt.Println("GetUserRankingWithCache ok")
	// 设置缓存（这里需要根据你的缓存实现来调整）
	m.CachedConn.SetCache(cacheKey, result)

	return result, nil
}
